﻿using System;
using System.Linq;
using System.Data.Linq;
using System.Data;
using UniMana.Data;
using UniMana.Utils.Data;
namespace UniMana.Data
{
    public static class Catalogue_CourseData
    {
        /// <summary>
        /// Lấy khóa học với khoa
        /// </summary>
        /// <param name="nCourse"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iDepartment"></param>
        /// <returns></returns>        
        public static IQueryable GetCourseByDepartment(
            this Table<Catalogue_Course> nCourse, 
            Table<Training_TrainingProgram> nTrainingProgram, 
            Table<Catalogue_Major> nMajor, 
            int m_iDepartment)
        {
            var course = (from t in nTrainingProgram
                          join t0 in nMajor on t.Catalogue_MajorKey equals t0.Catalogue_MajorKey
                          join t1 in nCourse on t.Catalogue_CourseKey equals t1.Catalogue_CourseKey
                          where
                            t0.Catalogue_DepartmentKey == m_iDepartment
                          select new
                          {
                              t.Catalogue_CourseKey,
                              t1.Name,
                          }).Distinct();
            return course.Distinct();
        }
        public static DataTable GetYearLasted(
            this Table<Catalogue_Course> nCouse, 
            int numRows = 10,
            int _iStyle= -0)
        {
            DateTime now = UniMana.Data.SysDateTime.Now;
            DataTable dt = new DataTable();
            if (_iStyle > 0)
            {
                var course = nCouse.Where(p => p.Style == _iStyle).OrderBy(p => p.Name);
                dt = course.ToDataTable();
            }
            else
            {
                var course = nCouse;
                dt = course.ToDataTable();
            }
            /*
            if (dt.Columns.Count > 0)
            {
                for (int i = dt.Rows.Count-1; i > 0; i--)
                {
                    if (dt.Rows[i]["Name"].ToString().IndexOf(now.Year.ToString()) > -1)
                    {
                        break;
                    }
                    else
                    {
                        dt.Rows.RemoveAt(i);
                    }
                }
            }
            if (dt.Rows.Count > numRows)
            {
                while (dt.Rows.Count > numRows)
                {
                    dt.Rows.RemoveAt(0);
                }
            }*/
            return dt;
        }
        /// <summary>
        /// Lấy khóa học với bậc đào tạo
        /// </summary>
        /// <param name="nCourse"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iLevel"></param>
        /// <returns></returns>
        public static IQueryable GetCourseByLevel(
            this Table<Catalogue_Course> nCourse, 
            Table<Training_TrainingProgram> nTrainingProgram, 
            Table<Catalogue_Major> nMajor, 
            int m_iLevel)
        {
            var course = (from t in nTrainingProgram
                          join t0 in nMajor on t.Catalogue_MajorKey equals t0.Catalogue_MajorKey
                          join t1 in nCourse on t.Catalogue_CourseKey equals t1.Catalogue_CourseKey 
                          where
                            t.Catalogue_LevelKey == m_iLevel
                          select new
                          {
                              t.Catalogue_CourseKey,
                              t1.Name,
                          }).Distinct();
            return course.Distinct();
        }
        /// <summary>
        /// Lấy khóa học với ngành
        /// </summary>
        /// <param name="nCourse"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iMajor"></param>
        /// <returns></returns>
        public static IQueryable GetCourseByMajor(
            this Table<Catalogue_Course> nCourse, 
            Table<Training_TrainingProgram> nTrainingProgram, 
            Table<Catalogue_Major> nMajor, 
            int m_iMajor)
        {
            var course = (from t in nTrainingProgram
                          join t0 in nMajor on t.Catalogue_MajorKey equals t0.Catalogue_MajorKey 
                          join t1 in nCourse on t.Catalogue_CourseKey equals t1.Catalogue_CourseKey 
                          where
                            t.Catalogue_MajorKey == m_iMajor
                          select new
                          {
                              t.Catalogue_CourseKey,
                              t1.Name,
                          }).Distinct();
            return course.Distinct();
        }
        /// <summary>
        /// Lấy khóa học với loại hình đào tạo
        /// </summary>
        /// <param name="nCourse"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iTrainingType"></param>
        /// <returns></returns>
        public static IQueryable GetCourseByTrainingType(
            this Table<Catalogue_Course> nCourse, 
            Table<Training_TrainingProgram> nTrainingProgram, 
            Table<Catalogue_Major> nMajor, 
            int m_iTrainingType)
        {
            var course = (from t in nTrainingProgram
                          join t0 in nMajor on t.Catalogue_MajorKey equals t0.Catalogue_MajorKey 
                          join t1 in nCourse on t.Catalogue_CourseKey equals  t1.Catalogue_CourseKey 
                          where
                            t.Catalogue_TrainingTypeKey == m_iTrainingType
                          select new
                          {
                              Catalogue_CourseKey = (System.Int32?)t.Catalogue_CourseKey,
                              t1.Name,
                          }).Distinct();
            return course.Distinct();
        }
        /// <summary>
        /// Lấy khóa học có trong chương trình đào tạo
        /// </summary>
        /// <param name="nCourse"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <returns></returns>
        public static IQueryable GetCourseInTrainingProgram(
            this Table<Catalogue_Course> nCourse, 
            Table<Training_TrainingProgram> nTrainingProgram, 
            Table<Catalogue_Major> nMajor)
        {
            var course = (from t in nTrainingProgram
                          join t0 in nMajor on t.Catalogue_MajorKey equals t0.Catalogue_MajorKey
                          join t1 in nCourse on t.Catalogue_CourseKey equals t1.Catalogue_CourseKey
                          select new
                          {
                              t.Catalogue_CourseKey,
                              t1.Name
                          }).Distinct();
            return course.Distinct();
        }
        /// <summary>
        /// Lấy khóa học với khoa, bậc đào tạo, ngành, loại hình
        /// </summary>
        /// <param name="nCourse"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="lueCatalogueDepartment"></param>
        /// <param name="lueCatalogueLevel"></param>
        /// <param name="lueCatalogueMajor"></param>
        /// <param name="lueCatalogueTrainingType"></param>
        /// <returns></returns>
        public static IQueryable GetCourseByDepartmentLevelMajorTrainingType(
            this Table<Catalogue_Course> nCourse, 
            Table<Training_TrainingProgram> nTrainingProgram, 
            Table<Catalogue_Major> nMajor, 
            int m_iDepartment, 
            int m_iLevel, 
            int m_iMajor, 
            int m_iTrainingType)
        {
            var course = from t in nTrainingProgram
                         join t0 in nMajor on t.Catalogue_MajorKey equals t0.Catalogue_MajorKey 
                         join t1 in nCourse on t.Catalogue_CourseKey equals t1.Catalogue_CourseKey 
                         select new
                         {
                             t.Catalogue_LevelKey,
                             t.Catalogue_MajorKey,
                             t.Catalogue_TrainingTypeKey,
                             t.Catalogue_CourseKey,
                             t0.Catalogue_DepartmentKey,
                             t1.Name
                         };
            if (m_iDepartment > 0)
            {
                course = course.Where(p => p.Catalogue_DepartmentKey == int.Parse(m_iDepartment.ToString()));
            }
            if (m_iLevel > 0)
            {
                course = course.Where(p => p.Catalogue_LevelKey == int.Parse(m_iLevel.ToString()));
            }
            if (m_iMajor > 0)
            {
                course = course.Where(p => p.Catalogue_MajorKey == int.Parse(m_iMajor.ToString()));
            }
            if (m_iTrainingType > 0)
            {
                course = course.Where(p => p.Catalogue_TrainingTypeKey == int.Parse(m_iTrainingType.ToString()));
            }

            var a = from cour in course
                    select new
                    {
                        cour.Catalogue_CourseKey,
                        cour.Name
                    };

            return a.Distinct();
        }

        #region [An]
        /// <summary>
        /// Lấy khóa học có trong chương trình đào tạo
        /// </summary>
        /// <param name="nCourse"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <returns></returns>

        public static IQueryable GetCourseKeyNameInTrainingProgram(this Table<Catalogue_Course> nCatalogue_Course,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nCatalogue_Major)
        {
            var course = (from t in nTraining_TrainingProgram
                          join t1 in nCatalogue_Course on t.Catalogue_CourseKey equals t1.Catalogue_CourseKey
                          select new
                          {
                              t.Catalogue_CourseKey,
                              t1.Name
                          }).Distinct();
            return course.Distinct();
        }
        /// <summary>
        /// Lấy tên  với mã
        /// </summary>
        /// <param name="nCatalogue_TrainingType"></param>
        /// <param name="m_iTypeKey"></param>
        /// <returns></returns>

        public static string ReturnNameWithKey(this Table<Catalogue_Course> nCatalogue_Course,
            int m_iKey)
        {
            var values = (from t in nCatalogue_Course
                          where t.Catalogue_CourseKey == m_iKey
                          select new
                          {
                              t.Name
                          }).Distinct().FirstOrDefault();
            return values.Name.ToString();
        }
        #endregion
    }
}
